<?php
/**
 * Schnitstellenklasse fuer das Nestedsets verfahren um Baeume in einer Datenbank darzustellen
 * @author Gabriel Bretschner <gabriel-bretschner@gmx.net>
 * @copyright 2008
 * @package Kanedo Library
 * @see Kanedo_Db
 *
 */
class Kanedo_NestedSets 
{
	/**
	 * Datenbankverbindung
	 *
	 * @var Kanedo_Db
	 */
	protected $_db = null;
	/**
	 * Tabellenname
	 *
	 * @var string
	 */
	protected $_table = null;
	/**
	 * Primary Key der Tabelle
	 *
	 * @var string
	 */
	protected $_primaryKey = "id_navigation";
	
	/**
	 * Das Namensfeld in der Tabelle
	 *
	 * @var string
	 */
	protected $_name = "name";
	
	/**
	 * Konstruktor
	 *
	 * @param Kanedo_Db $db
	 * @param string $table
	 * @param string $pk
	 */
	public function __construct(Kanedo_Db $db, $table, $pk = "id_navigation", $name = "name")
	{
		$this->_db = $db;
		if (!$this->_db->isConnected())
		{
			throw new Kanedo_Db_Exception("no_db_connection");
		}
		
		$this->_table = $table;
		$this->_primaryKey = $pk;
		$this->_name = $name;
		
	}
	
	/**
	 * Funktion um Wurzelknoten zu erstellen. Im Normalfall nicht so oft verwendet
	 *
	 * @param string $name
	 */
	public function createRootNode($name)
	{
		$sql = "INSERT INTO `".$this->_table."` (".$this->_name.", lft, rgt) VALUES ('".$name."', 1,2);";
		$this->_db->executeQuery($sql);
	}
	public function getRootNode()
	{
		$sql = "SELECT * FROM ".$this->_table." WHERE `lft` = 1";
		$result = $this->_db->executeQuery($sql);
		$root = $this->_db->fetchAll($result);
		return $root[0];
	}
	
	public function insertFirstChild($name)
	{
		$root = $this->getRootNode();
		$sql = "UPDATE ".$this->_table." SET rgt = rgt + 2 WHERE rgt = ".$root['rgt'].";";
		$this->_db->executeQuery($sql);
		$sql = "INSERT INTO ".$this->_table." (".$this->_name.",lft,rgt) VALUES ('".$name."', ".$root['rgt'].",".$root['rgt']."+1);";
		$this->_db->executeQuery($sql);
	}
		
	/**
	 * Kinderknoten einfügen
	 *
	 * @param string $name
	 * @param int $lft
	 * @param int $rgt
	 */
	public function insertNode($name, $lft, $rgt)
	{
		$sql = "UPDATE ".$this->_table." SET rgt = rgt +2 WHERE rgt >= ".$rgt.";";
		$this->_db->executeQuery($sql);
		$sql = "UPDATE ".$this->_table." SET lft = lft +2 WHERE lft > ".$rgt.";";
		$this->_db->executeQuery($sql);
		$sql = "INSERT INTO ".$this->_table." (".$this->_name.", lft,rgt) VALUES('".$name."', ".$rgt.", ".$rgt."+1);";
		$this->_db->executeQuery($sql);
	}
	public function insertChildNode($name, $parent)
	{
		$parentNode = $this->getNodeById($parent);
		$this->insertNode($name, $parentNode['lft'],$parentNode['rgt']);	
	}
	
	/**
	 * Liesst Kntoen aus
	 *
	 * @param int $id
	 */
	public function getNodeById($id)
	{
		$sql = "SELECT `lft`, `rgt`, `".$this->_name."` FROM `".$this->_table."` WHERE `".$this->_primaryKey."` =".$id;
		$result = $this->_db->executeQuery($sql);
		$node = $this->_db->fetchAll($result);
		if(key_exists(0, $node)) {
		return $node[0];
		}else{
			require_once 'Kanedo/NestedSets/Exception.php';
			throw new Kanedo_NestedSets_Exception("no_node");
		}
	}

	/**
	 * liesst den gesamten Baum aus
	 *
	 */
	public function getTree()
	{
		$sql = "SELECT n.".$this->_primaryKey.", n.".$this->_name.", COUNT(*)-1 AS level FROM ".$this->_table." AS n, ".$this->_table." AS p WHERE n.lft BETWEEN p.lft AND p.rgt GROUP BY n.lft ORDER BY n.lft;";
		$return = $this->_db->executeQuery($sql);
		$data =  $this->_db->fetchAll($return);
		
	}

	/**
	 * Entfernt einen Knoten aus dem Baum
	 * Alle Kindknoten gehen verloren
	 *
	 * @param int $id
	 */
	public function removeNode($id)
	{
		$node = $this->getNodeById($id);
		$sql = "DELETE FROM `".$this->_table."` WHERE lft BETWEEN ".$node['lft']." AND ".$node['rgt'].";";
		$this->_db->executeQuery($sql);
		$sql = "UPDATE `".$this->_table."` SET lft=lft-ROUND((".$node['rgt']."-".$node['lft']."+1)) WHERE lft>".$node['rgt'].";";
		$this->_db->executeQuery($sql);
		$sql =	"UPDATE `".$this->_table."` SET rgt=rgt-ROUND((".$node['rgt']."-".$node['lft']."+1)) WHERE rgt>".$node['rgt'].";";
		$this->_db->executeQuery($sql);
	}
	
	/**
	 * Entfernt einen Knoten aus dem Baum und alle seine Kindknoten werden um ein Stufe höher gesetzt und gehen nicht verloren
	 *
	 * @param int $id
	 */
	public function removeIndividualNode($id)
	{
		$node = $this->getNodeById($id);
		$sql = "DELETE FROM `".$this->_table."` WHERE lft = ".$node['lft'].";";
		$this->_db->executeQuery($sql);
		$sql = "UPDATE `".$this->_table."` SET lft=lft-1, rgt=rgt-1 WHERE lft BETWEEN ".$node['lft']." AND ".$node['rgt'].";";
		$this->_db->executeQuery($sql);
		$sql = "UPDATE `".$this->_table."` SET lft=lft-2 WHERE lft>".$node['rgt'].";";
		$this->_db->executeQuery($sql);
		$sql = "UPDATE `".$this->_table."` SET rgt=rgt-2 WHERE rgt>".$node['rgt'].";";
		$this->_db->executeQuery($sql);
		
	}
	
	/**
	 * Liesst den Pfad zu einem Knoten aus
	 *
	 * @param string $id
	 * @return array
	 */
	public function getPathToNode($id)
	{
		$sql = "SELECT p.*
				    FROM `".$this->_table."` n,
				         `".$this->_table."` p
				   WHERE n.lft BETWEEN p.lft AND p.rgt
				     AND n.".$this->_primaryKey." = ".$id."
				ORDER BY n.lft;";
		$result = $this->_db->executeQuery($sql);
		return $this->_db->fetchAll($result);
	}
	
	
} 
?>